﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using DB.SqlHelper;
using Framework.Common.Common;
using Mode;

namespace Framework.DataAccess
{
    public  class ExamOlineDA
    {
         /// <summary>
        /// 
        /// </summary>
        private readonly DbInstance db;

        public ExamOlineDA()
        {
            this.db = DbManager.GetDbInstance();
        }

        /// <summary>
        /// 初始化考试
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="cdid"></param>
        /// <returns></returns>
        public int Initcandidate(int uid,int examinId)
        {
            //string strSql = "if not EXISTS(select id from candidate_table where member={0} and examination={1}) ";
            //strSql += " begin insert into candidate_table(member,examination,score,[time],[date],[status])values({0},{1},0,0,getdate(),0); ";
            //strSql += " SELECT CONVERT(Int,SCOPE_IDENTITY()) end else  select -99 ";

            string strSql = "if not EXISTS(select id from candidate_table where member={0} and examination={1} and [status]<>-2) begin ";
            strSql += "  update candidate_table set  [date]=getdate(),[status]=-1 where member={0} and examination={1} select  id from candidate_table where member={0} and examination={1} end  else  select -99 ";


            strSql = string.Format(strSql, uid, examinId);


           DbCommand cmd = db.GetSqlStringCommand(strSql);

          return (int)db.ExecuteScalar(cmd);
        }
        /// <summary>
        /// 修改考试时间
        /// </summary>
        /// <param name="cdid"></param>
        /// <param name="time"></param>
        /// <returns></returns>
        public int UpdateTime(int cdid,Int32 time)
        {
            string strSql = "update candidate_table set [time]=" + time + " where id=" + cdid;
            DbCommand cmd = db.GetSqlStringCommand(strSql);

            return db.ExecuteNonQuery(cmd);
        }


        /// <summary>
        /// 时时保存答案
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="cdid"></param>
        /// <param name="topicid"></param>
        /// <param name="anserid"></param>
        /// <param name="topicType"></param>
        /// <param name="isselect"></param>
        /// <param name="code"></param>
        /// <param name="anval"></param>
        /// <param name="dzscore"></param>
        /// <returns></returns>
        public int replyAnswer(int userid,int cdid,int topicid,int anserid,int topicType,int isselect,int code,string anval,int dzscore)
        {
         

            DbCommand cmd = db.GetStoredProcCommand("sp_Addreplytable");


            db.AddInParameter(cmd, "@userid", DbType.Int16, userid);
            db.AddInParameter(cmd, "@candidate", DbType.Int32,cdid);
            db.AddInParameter(cmd, "@topicid", DbType.Int32, topicid);
            db.AddInParameter(cmd, "@anserid", DbType.Int32, anserid);
            db.AddInParameter(cmd, "@topicType", DbType.Int32, topicType);
            db.AddInParameter(cmd, "@isselect", DbType.Int32, isselect);
            db.AddInParameter(cmd, "@code", DbType.Int32,code);
            db.AddInParameter(cmd, "@anval", DbType.String, anval);
            db.AddInParameter(cmd, "@dzscore", DbType.Int32, dzscore);
            db.AddOutParameter(cmd, "@erro", DbType.Int32, 4);
            db.ExecuteNonQuery(cmd);

            int err = db.GetParameterValue(cmd, "@erro").ToInt(0);
            return err;
        }

        /// <summary>
        /// 交卷时提交所有考题及答案
        /// </summary>
        /// <param name="candidate"></param>
        /// <param name="sheetReplyList"></param>
        /// <returns></returns>
        public void PostAllTopicReply(int candidate, List<sheet_reply> sheetReplyList)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("declare @sheetid int;\r\n ");
            foreach (sheet_reply sheet in sheetReplyList)
            {
                sbSql.AppendFormat("select @sheetid=id from sheet_table where candidate={0} and topic={1};\r\n", candidate, sheet.topic);
                if (sheet.topicType == 1 || sheet.topicType == 3)
                {
                    //单，判断
                    int singleAnswerID = -1;
                    if (sheet.replyList.Count > 0)
                    {
                        sbSql.AppendFormat("If Not Exists(Select id From reply_table Where sheet=@sheetid And answerid={0}) Insert Into reply_table(sheet,answerid)Values(@sheetid,{0});\r\n", sheet.replyList[0].answerid);
                        singleAnswerID = sheet.replyList[0].answerid;
                    }
                    //删除选择外的其他选项,如果没有选项则删除全部否则删除选择之外的
                    sbSql.AppendFormat("Delete reply_table Where sheet=@sheetid {0};\r\n", singleAnswerID != -1 ? string.Format(" And answerid<>{0}", singleAnswerID) : "");
                }
                else if (sheet.topicType == 2)
                {
                    //多
                    string answerIDs = "";
                    foreach (reply_info reply in sheet.replyList)
                    {
                        sbSql.AppendFormat("If Not Exists(Select id From reply_table Where sheet=@sheetid And answerid={0}) Insert Into reply_table(sheet,answerid)Values(@sheetid,{0});\r\n", reply.answerid);
                        answerIDs += reply.answerid + ",";
                    }
                    //删除选择外的其他选项,如果没有选项则删除全部否则删除选择之外的
                    sbSql.AppendFormat("Delete reply_table Where sheet=@sheetid {0};\r\n", answerIDs.Length > 0 ? string.Format(" And answerid Not In({0})", answerIDs.TrimEnd(',')) : "");
                }
                else if (sheet.topicType == 4)
                {
                    //填
                    string answerIDs = "";
                    foreach (reply_info reply in sheet.replyList)
                    {
                        sbSql.AppendFormat("If Not Exists(Select id From reply_table Where sheet=@sheetid And answerid={0}) Insert Into reply_table(sheet,answerid,reply)Values(@sheetid,{0},'{1}');\r\n Else Update reply_table Set reply='{1}' where sheet=@sheetid and answerid={0};\r\n ", reply.answerid, reply.reply.Replace("'", "''"));
                        answerIDs += reply.answerid + ",";
                    }
                    //删除选择外的其他选项,如果没有选项则删除全部否则删除选择之外的
                    sbSql.AppendFormat("Delete reply_table Where sheet=@sheetid {0};\r\n", answerIDs.Length > 0 ? string.Format(" And answerid Not In({0})", answerIDs.TrimEnd(',')) : "");
                }
                else if (sheet.topicType == 5)
                {
                    //问
                    int singleAnswerID = -1;
                    if (sheet.replyList.Count > 0)
                    {
                        sbSql.AppendFormat("If Not Exists(Select id From reply_table Where sheet=@sheetid And answerid={0}) Insert Into reply_table(sheet,answerid,reply)Values(@sheetid,{0},'{1}');\r\n Else Update reply_table Set reply='{1}' where sheet=@sheetid and answerid={0};\r\n ", sheet.replyList[0].answerid, sheet.replyList[0].reply.Replace("'", "''"));
                        singleAnswerID = sheet.replyList[0].answerid;
                    }
                    //删除选择外的其他选项,如果没有选项则删除全部否则删除选择之外的
                    sbSql.AppendFormat("Delete reply_table Where sheet=@sheetid {0};\r\n", singleAnswerID != -1 ? string.Format(" And answerid<>{0}", singleAnswerID) : "");
                }
                else if (sheet.topicType == 6)
                {
                    //打字
                    sbSql.AppendFormat("update sheet_table set score={0} where id=@sheetid;\r\n", sheet.score);
                }
            }
            if (sbSql.Length > 0)
            {
                //不开启事务
                //DbManager.BeginTransaction();
                DbInstance myDB = DbManager.GetDbInstance();
                try
                {
                    myDB.ExecuteNonQueryBySql(sbSql.ToString());
                    //DbManager.Commit();
                }
                catch (Exception e)
                {
                    //DbManager.Rollback();
                    throw e;
                }
            }
        }
    }
}
